package com.apes.autoparts.searcher.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.plugin.esb.api.SimpleRequest;
import com.apes.framework.query.ApesDao;
import com.apes.framework.query.SqlService;
import com.apes.framework.rop.converter.DateUtils;
import com.apes.framework.util.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.Date;

@Component
public class AutopartsDao extends ApesDao {

    @Autowired
    SqlService sqlService;

    /***
     * 查询EPC_VIN_ID
     */
    public JSONArray checkVIN_EPC_ID(JSONObject param) {
        String sSQL = "SELECT A.EPC_BBMC, A.MY_MODEL_STD_ID, A.VIN, A.TS01, A.LY, A.LRR,\n" +
                "          A.YXBJ\n" +
                "     FROM SJZX.VIN_EPC_ID A\n" +
                "    WHERE A.VIN = :vin\n" +
                "      AND A.YXBJ = 1";
        return toJSONArray(this.find(sSQL, param));
    }

    /***
     * 查询CHX_EPC_ID
     */
    public JSONArray checkCHX_EPC_ID(JSONObject param) {
        String sSQL = "SELECT A.EPC_BBMC, A.MY_MODEL_STD_ID, A.PZCXID, A.QCCPMC,\n" +
                "             A.TS01, A.LY, A.LRR, A.YXBJ\n" +
                "        FROM SJZX.CHX_EPC_ID A\n" +
                "       WHERE A.PZCXID = :vehicleConfig\n" +
                "         AND A.QCCPMC = :brandName\n" +
                "         AND A.YXBJ = 1";
        return toJSONArray(this.find(sSQL, param));
    }


    /***
     * 插入EPC_VIN_ID
     */

    public void insertEPC_VIN_ID(JSONObject param, JSONObject insertData) {
        JSONObject tempJson = new JSONObject();
        tempJson.putAll(insertData);
        tempJson.put("VIN", param.getString("vin"));
        tempJson.put("LRR", "ADMIN");
        tempJson.put("LY", "车型对照");
        tempJson.put("TS01", DateUtil.format(new Date(), "yyyyMMdd.HHmmss.SSS"));
    }

    /***
     * 查询当前epc车型和版本对应的标准品类信息
     */

    public JSONArray getStardardCatalog(JSONObject param) {
        String sSQL = "SELECT B.PLID\n" +
                "  FROM SJZX.EPC_STD_CATA A, SJZX.EPC_STD_CATA_DZB B\n" +
                " WHERE A.MJBJ = 1\n" +
                "   AND A.EPC_BBMC = B.EPC_BBMC\n" +
                "   AND A.CATA_LEVEL = B.CATA_LEVEL\n" +
                "   AND A.CATA_INDEX = B.CATA_INDEX\n" +
                "   AND A.CURRENT_ID = B.CURRENT_ID\n" +
                "   AND A.EPC_BBMC = :epcVersion\n" +
                "   AND A.MY_MODEL_STD_ID = :epcId\n" +
                "   AND B.PLID IS NOT NULL\n" +
                " GROUP BY B.PLID";
        return toJSONArray(this.find(sSQL, param));
    }


    /***
     * 查询当前epc车型和版本对应的epc目录主组
     */
    public JSONArray getEpcMenu(JSONObject param) {
        String sSQL = "SELECT A.CATA_ID, A.EPC_BBMC, A.MY_MODEL_STD_ID, A.CATA_LEVEL, A.CATA_INDEX,\n" +
                "       A.CURRENT_ID, A.NAME, A.NAME_ZH, A.NAME_ZH_ORIGINAL, REPLACE(A.IMGNAME,'.tif','.TIF') AS IMGNAME,\n" +
                "       A.PREV_ID, A.MJBJ, A.TS01, A.LY, A.KSRQ, A.JSRQ, A.REMARK1, A.REMARK2\n" +
                "  FROM SJZX.EPC_STD_CATA A\n" +
                " WHERE A.MY_MODEL_STD_ID = :epcId\n" +
                "   AND A.EPC_BBMC = :epcVersion AND A.MJBJ <> 1 ORDER BY A.CATA_INDEX";
        return toJSONArray(this.find(sSQL, param));
    }



    /***
     * 查询当前品类列表数据
     */

    public JSONArray queryCurrentPlxx(JSONArray array) {
        JSONArray ids = new JSONArray();
        for( int i = 0, len = array.size(); i < len; i++ ) {
            ids.add(array.getJSONObject(i).getString("PLID"));
        }
        String sSQL = "SELECT A.* FROM ( SELECT T.* FROM SJZXERP.MAS_PLXX T WHERE T.PLID IN (" + translateArrayToString(ids, ",") + ") ) A ORDER BY A.PLID";
        return toJSONArray(this.find(sSQL));
    }


    /***
     * 查询上一级的品类并去重
     */
    public JSONArray queryParentUnique(JSONArray array) {
        JSONArray ids = new JSONArray();
        for( int i = 0, len = array.size(); i < len; i++ ) {
            ids.add(array.getJSONObject(i).getString("PLID"));
        }
        String sSQL = "SELECT A.* FROM ( SELECT B.* FROM ( SELECT T.SJID FROM MAS_PLXX T WHERE T.PLID IN (" + translateArrayToString(ids, ",") + ") " +
                " GROUP BY T.SJID) A, MAS_PLXX B WHERE A.SJID = B.PLID) A ORDER BY A.PLID";

        return toJSONArray(this.find(sSQL));
    }


    /***
     * 关联查询导入搜索引擎
     * @param param
     * @return
     */
    public JSONArray queryEpcMenuFromOracle(JSONObject param) {
        param.put("TS01", "20190722.120000.000");
        String sSQL = "SELECT * FROM (SELECT A.CATA_ID, A.EPC_BBMC, A.MY_MODEL_STD_ID, A.CATA_LEVEL, A.CATA_INDEX,\n" +
                "       A.CURRENT_ID, A.NAME, A.NAME_ZH, A.NAME_ZH_ORIGINAL, REPLACE(A.IMGNAME,'.tif','.TIF') AS IMGNAME,\n" +
                "       A.PREV_ID, A.MJBJ, A.TS01, A.LY, A.KSRQ, A.JSRQ, A.REMARK1, A.REMARK2,\n" +
                "       B.PLID\n" +
                "  FROM SJZX.EPC_STD_CATA A, SJZX.EPC_STD_CATA_DZB B\n" +
                " WHERE A.EPC_BBMC = B.EPC_BBMC\n" +
                "   AND A.CATA_LEVEL = B.CATA_LEVEL\n" +
                "   AND A.CATA_INDEX = B.CATA_INDEX\n" +
                "   AND A.CURRENT_ID = B.CURRENT_ID\n" +
                "   AND A.TS01 > :TS01 ORDER BY A.TS01) WHERE ROWNUM <= 1000";
        return toJSONArray(this.find(sSQL, param));
    }


    /***
     * 将String数组转化为String
     * @param array
     * @param regex
     * @return
     */
    private String translateArrayToString(JSONArray array, String regex) {
        String returnStr = "";
        for (int i = 0; i < array.size(); i++) {
            if (i != array.size() - 1) {
                returnStr =  returnStr + "'" + array.getString(i) + "'" + regex;
            } else {
                returnStr = returnStr + "'" + array.getString(i) + "'";
            }
        }
        return returnStr;
    }


    /***
     * epc 获取下一级
     */

    public JSONArray getSubgroupForEpc(JSONObject param) {
        String sSQL = "SELECT A.CATA_ID, A.EPC_BBMC, A.MY_MODEL_STD_ID, A.CATA_LEVEL,\n" +
                "          A.CATA_INDEX, A.CURRENT_ID, A.NAME, A.NAME_ZH, A.NAME_ZH_ORIGINAL,\n" +
                "          REPLACE(A.IMGNAME,'.tif','.TIF') AS IMGNAME, A.PREV_ID, A.MJBJ, A.TS01, A.LY, A.KSRQ, A.JSRQ,\n" +
                "          A.REMARK1, A.REMARK2\n" +
                "     FROM SJZX.EPC_STD_CATA A\n" +
                "     WHERE A.MY_MODEL_STD_ID = :epcId\n" +
                "     AND A.EPC_BBMC = :epcVersion\n" +
                "     AND A.PREV_ID = :id" +
                "     AND A.CATA_LEVEL = " + ( param.getIntValue("level")  + 1 ) + " ORDER BY A.CATA_INDEX";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray getSubgroupForOwner(JSONObject param) {
        String sSQL = "SELECT A.CATA_ID, A.EPC_BBMC, A.MY_MODEL_STD_ID, A.CATA_LEVEL,\n" +
                "          A.CATA_INDEX, A.CURRENT_ID, A.NAME, A.NAME_ZH, A.NAME_ZH_ORIGINAL,\n" +
                "          REPLACE(A.IMGNAME,'.tif','.TIF') AS IMGNAME, A.PREV_ID, A.MJBJ, A.TS01, A.LY, A.KSRQ, A.JSRQ,\n" +
                "          A.REMARK1, A.REMARK2\n" +
                "     FROM SJZX.EPC_STD_CATA A, SJZX.EPC_STD_CATA_DZB B\n" +
                "     WHERE A.EPC_BBMC = B.EPC_BBMC\n" +
                "     AND A.CATA_LEVEL = B.CATA_LEVEL\n" +
                "     AND A.CATA_INDEX = B.CATA_INDEX\n" +
                "     AND A.CURRENT_ID = B.CURRENT_ID\n" +
                "     AND A.MY_MODEL_STD_ID = :epcId\n" +
                "     AND A.EPC_BBMC = :epcVersion\n" +
                "     AND A.MJBJ = 1\n" +
                "     AND B.PLID = :id" +
                "     ORDER BY A.CATA_INDEX";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * epc 获取当前目录（多id）
     * @param param
     * @return
     */

    public JSONArray getSubgroupForEpc(JSONObject param, JSONArray list) {
        String sSQL = "SELECT A.CATA_ID, A.EPC_BBMC, A.MY_MODEL_STD_ID, A.CATA_LEVEL,\n" +
                "          A.CATA_INDEX, A.CURRENT_ID, A.NAME, A.NAME_ZH, A.NAME_ZH_ORIGINAL,\n" +
                "          REPLACE(A.IMGNAME,'.tif','.TIF') AS IMGNAME, A.PREV_ID, A.MJBJ, A.TS01, A.LY, A.KSRQ, A.JSRQ,\n" +
                "          A.REMARK1, A.REMARK2\n" +
                "     FROM SJZX.EPC_STD_CATA A\n" +
                "     WHERE A.MY_MODEL_STD_ID = :epcId\n" +
                "     AND A.EPC_BBMC = :epcVersion\n" +
                "     AND A.CURRENT_ID IN (" + translateArrayToString(list, ",") + ") ORDER BY A.CATA_INDEX";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 解析关键词（partNumber partDescId）
     */

    public JSONArray parseKeywordForPartNumber(JSONObject param) {
        String sSQL = "SELECT A.CURRENT_ID\n" +
                "  FROM SJZX.EPC_"+ param.getString("epcVersion") +" A\n" +
                " WHERE A.MY_MODEL_STD_ID = :epcId\n" +
                "   AND A.PARTNUMBER = :keyword\n" +
                "GROUP BY A.CURRENT_ID";
        return toJSONArray(find(sSQL, param));
    }

    public JSONArray parseKeywordPartDescId(JSONObject param) {
        String sSQL = "SELECT A.CURRENT_ID\n" +
                "  FROM SJZX.EPC_"+ param.getString("epcVersion") +" A, EPC_OE_PJMC B\n" +
                " WHERE A.PARTNUMBER = B.PARTNUMBER\n" +
                "   AND B.EPC_BBMC = :epcVersion\n" +
                "   AND B.PJMCID = :keyword\n" +
                "   AND A.MY_MODEL_STD_ID = :epcId\n" +
                " GROUP BY A.CURRENT_ID";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 根据目录名查询目录
     */

    public JSONArray getSubgroupByMenuName(JSONObject param) {
        String sSQL = "SELECT A.CATA_ID, A.EPC_BBMC, A.MY_MODEL_STD_ID, A.CATA_LEVEL, A.CATA_INDEX,\n" +
                "       A.CURRENT_ID, A.NAME, A.NAME_ZH, A.NAME_ZH_ORIGINAL, REPLACE(A.IMGNAME,'.tif','.TIF') AS IMGNAME,\n" +
                "       A.PREV_ID, A.MJBJ, A.TS01, A.LY, A.KSRQ, A.JSRQ, A.REMARK1, A.REMARK2\n" +
                "  FROM SJZX.EPC_STD_CATA A\n" +
                " WHERE A.MY_MODEL_STD_ID = :epcId\n" +
                "   AND A.EPC_BBMC = :epcVersion\n" +
                "   AND A.MJBJ = 1\n" +
                "   AND A.NAME_ZH LIKE '%" + param.getString("menuName") + "%'\n" +
                " ORDER BY A.CATA_INDEX";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 获取配件信息（关联配件表和编译表）
     */
    public JSONArray getAnalysisParts(JSONObject param , String tableName) {

        String sSQL = "SELECT * FROM (\n" +
                "  SELECT A.PARTS_ID, A.MY_MODEL_STD_ID, A.CURRENT_ID, A.PNC, A.PARTNUMBER,\n" +
                "         A.PARTNAME, A.PARTNAME_ZH, A.QTY, A.REMARK1, A.REMARK2, A.REPLACEMENT,\n" +
                "         A.COST_4S, A.PRICE_4S, A.PRICEUPDATEDAT, A.LY, B.EPC_BBMC, B.PJMCID,\n" +
                "         B.PJMC, B.BYBJ,\n" +
                "         TO_CHAR(TO_TIMESTAMP(B.TS01, 'YYYYMMDD.HH24MISS.FF3'), 'YYYY-MM-DD') TS01\n" +
                "         , C.OEMS, C.IMGNAME\n" +
                "    FROM SJZX."+ tableName +" A, EPC_OE_PJMC B, SJZX.EPC_PICTURE C\n" +
                "   WHERE A.PARTNUMBER = B.PARTNUMBER(+)\n" +
                "     AND A.PARTNUMBER = C.PARTNUMBER(+)\n" +
                "     AND A.MY_MODEL_STD_ID = :epcId\n" +
                "     AND B.EPC_BBMC = :epcVersion\n" +
                "     AND A.CURRENT_ID = :id\n" +
                "  union all \n" +
                "  SELECT A.PARTS_ID, A.MY_MODEL_STD_ID, A.CURRENT_ID, A.PNC, A.PARTNUMBER,\n" +
                "         A.PARTNAME, A.PARTNAME_ZH, A.QTY, A.REMARK1, A.REMARK2, A.REPLACEMENT,\n" +
                "         A.COST_4S, A.PRICE_4S, A.PRICEUPDATEDAT, A.LY, '' EPC_BBMC, '' PJMCID,\n" +
                "         '' PJMC, '0' BYBJ, 'OE号不存在' TS01\n" +
                "         , '' OEMS, '' IMGNAME\n" +
                "    FROM SJZX."+ tableName +" A\n" +
                "   WHERE A.MY_MODEL_STD_ID = :epcId\n" +
                "     AND A.CURRENT_ID = :id\n" +
                "     AND A.PARTNUMBER IS NULL\n" +
                ") ORDER BY PARTS_ID";

//        String sSQL = "SELECT A.PARTS_ID, A.MY_MODEL_STD_ID, A.CURRENT_ID, A.PNC, A.PARTNUMBER,\n" +
//                "       A.PARTNAME, A.PARTNAME_ZH, A.QTY, A.REMARK1, A.REMARK2, A.REPLACEMENT,\n" +
//                "       A.COST_4S, A.PRICE_4S, A.PRICEUPDATEDAT, A.LY, B.EPC_BBMC, B.PJMCID,\n" +
//                "       B.PJMC, B.BYBJ, TO_CHAR(TO_TIMESTAMP(B.TS01,'YYYYMMDD.HH24MISS.FF3'),'YYYY-MM-DD') TS01, C.OEMS, C.IMGNAME\n" +
//                "  FROM SJZX."+ tableName +" A, EPC_OE_PJMC B, SJZX.EPC_PICTURE C\n" +
//                " WHERE A.PARTNUMBER = B.PARTNUMBER(+)\n" +
//                "   AND A.PARTNUMBER = C.PARTNUMBER(+)\n" +
//                "   AND A.MY_MODEL_STD_ID = :epcId\n" +
//                "   AND B.EPC_BBMC = :epcVersion\n" +
//                "   AND A.CURRENT_ID = :id ORDER BY A.PARTS_ID";
        return toJSONArray(find(sSQL, param));
    }


    /***
     * 查询配件关联的图片信息
     */
    public JSONArray getImageUrl(JSONObject param) {
        String sSQL = "SELECT A.PARTNUMBER, REPLACE(A.IMGNAME,'.tif','.TIF') AS IMGNAME, A.OEMS, A.LRR, A.TS01\n" +
                "    FROM SJZX.EPC_PICTURE A\n" +
                "   WHERE A.PARTNUMBER = :PARTNUMBER";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 获取商品
     */

    public JSONArray getProduct(JSONObject param) {
        String sSQL = "SELECT A.SPID, A.SPMC, A.PLID, A.PLMC, A.PPID, A.PPMC, A.CJID, A.CJMC,"
                + "       A.SPZID, A.SPZMC, A.SPZT, A.SPXH, A.GG, A.BZSM, A.CD, A.EANID, "
                + "       A.OEMID, A.IAMID, A.YS, A.JEHSBJ, A.SLHSBJ, A.VIPYELX, A.ZDYBJ, "
                + "       A.FWSPBJ, A.XZWZBJ, A.CXZKBJ, A.TS01, A.LRR, A.LRRQ_SJ, A.CGZID,"
                + "       B.JLDW, B.HSBL, A.SSLBID, A.SSLBMC, A.SB, A.SSBL, A.CGZMC, A.JSPID,"
                + "       A.DSFSPID, A.FFBJ, A.DMCJ, A.BZQX, A.BXQX, A.SGFS, A.SPZCLX,"
                + "       A.PJMCID, A.PJMC, A.PYM, A.FWLXID, A.FWLXMC,A.KZZCBJ,A.CPMS, A.KSFWBJ, A.SPID ERPSPID,"
                + "		  B.ZCMRBJ, B.CGMRBJ, B.XSMRBJ, B.HYDWBJ"
                + "  FROM MAS_SPXX A, MAS_SPBZ B"
                + " WHERE A.SPID=B.SPID AND B.HSBL=1 AND A.SPZT <> 3 "
                + "   AND A.PJMCID = #{PJMCID} AND ';'||A.OEMID||';' LIKE '%;'|| #{PARTNUMBER} ||';%' ";
        return toJSONArray(find(sSQL, param));
    }


    /***
     * 通过中文名 上级编码 版本获取所有的oe
     */

    public JSONArray searchParts(JSONObject param) {
        String sSQL = "SELECT T.*\n" +
                "  FROM SJZX.EPC_"+ param.getString("epcVersion") +" T\n" +
                " WHERE T.CURRENT_ID = :menuId\n" +
                "   AND T.PARTNAME_ZH = :partChineseName";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 通过epc版本  oe号查询配件名称表
     */

    public JSONArray searchPartParse(JSONObject param) {
        String sSQL = "SELECT T.*\n" +
                "  FROM EPC_OE_PJMC T\n" +
                " WHERE T.EPC_BBMC = :epcVersion\n" +
                "   AND T.PARTNUMBER = :partNumber";
        return toJSONArray(find(sSQL, param));
    }


    /***
     * 通过 epcVersion menuId menuName parChineseName 查询所有的满足条件的配件并做去重
     */
    public JSONArray searchPartsForDataCenterDailyResolve(JSONObject param) {
//        String sSQL = "SELECT C.*,\n" +
//                "       (SELECT LISTAGG(DECODE(T.PJMCID, NULL, NULL, T.PJMCID || ':' || T.PJMC), ';') WITHIN GROUP(ORDER BY T.EPC_BBMC, T.PARTNUMBER)" +
//                "           FROM EPC_OE_PJMC T\n" +
//                "          WHERE T.EPC_BBMC = :epcVersion\n" +
//                "            AND T.PARTNUMBER = C.PARTNUMBER) PJMC\n" +
//                "  FROM (SELECT A.PARTNUMBER, A.PNC, B.IMGNAME, B.OEMS\n" +
//                "           FROM (SELECT DISTINCT PNC, PARTNUMBER\n" +
//                "                    FROM SJZX.EPC_"+ param.getString("epcVersion") +"\n" +
//                "                   WHERE (CURRENT_ID, PARTNAME_ZH) IN\n" +
//                "                         (SELECT DISTINCT CURRENT_ID, :partChineseName\n" +
//                "                            FROM SJZX.EPC_STD_CATA\n" +
//                "                           WHERE EPC_BBMC = :epcVersion\n" +
//                "                             AND NAME_ZH = :menuName\n" +
//                "                             AND MJBJ = '1')) A, SJZX.EPC_PICTURE B\n" +
//                "          WHERE A.PARTNUMBER = B.PARTNUMBER(+)) C\n" +
//                " WHERE 1 = 1";

//        String sSQL = "SELECT C.*, D.OEMS, (SELECT LISTAGG(DECODE(T.PJMCID, NULL, NULL, T.PJMCID || ':' || T.PJMC),\n" +
//                "                         ';') WITHIN GROUP(ORDER BY T.EPC_BBMC, T.PARTNUMBER) \n" +
//                "           FROM EPC_OE_PJMC T\n" +
//                "          WHERE T.EPC_BBMC = :epcVersion\n" +
//                "            AND T.PARTNUMBER = C.PARTNUMBER) PJMC\n" +
//                "  FROM (SELECT DISTINCT A.PNC, B.IMGNAME, A.PARTNUMBER\n" +
//                "           FROM SJZX.EPC_"+ param.getString("epcVersion") +" A, SJZX.EPC_STD_CATA B\n" +
//                "          WHERE A.CURRENT_ID = B.CURRENT_ID\n" +
//                "            AND B.MJBJ = '1'\n" +
//                "            AND B.NAME_ZH = :menuName\n" +
//                "            AND B.EPC_BBMC = :epcVersion\n" +
//                "            AND A.PARTNAME_ZH = :partChineseName) C, SJZX.EPC_PICTURE D\n" +
//                " WHERE C.PARTNUMBER = D.PARTNUMBER(+)";


        String sSQL = "SELECT C.*, D.OEMS,\n" +
                "       (SELECT LISTAGG(DECODE(T.PJMCID, NULL, NULL, T.PJMCID || ':' || T.PJMC),\n" +
                "                         ';') WITHIN GROUP(ORDER BY T.EPC_BBMC, T.PARTNUMBER)\n" +
                "           FROM EPC_OE_PJMC T\n" +
                "          WHERE T.EPC_BBMC = :epcVersion\n" +
                "            AND T.PARTNUMBER = C.PARTNUMBER) PJMC,\n" +
                "       (SELECT MAX(T.TS01)\n" +
                "           FROM EPC_OE_PJMC T\n" +
                "          WHERE T.EPC_BBMC = :epcVersion\n" +
                "            AND T.PARTNUMBER = C.PARTNUMBER) TS01\n" +
                "  FROM (SELECT DISTINCT A.PNC, B.IMGNAME, A.PARTNUMBER\n" +
                "           FROM SJZX.EPC_"+ param.getString("epcVersion") +" A, SJZX.EPC_STD_CATA B\n" +
                "          WHERE A.CURRENT_ID = B.CURRENT_ID\n" +
                "            AND A.MY_MODEL_STD_ID = B.MY_MODEL_STD_ID\n" +
                "            AND B.MJBJ = '1'\n" +
                "            AND B.NAME_ZH = :menuName\n" +
                "            AND B.EPC_BBMC = :epcVersion\n" +
                "            AND A.PARTNAME_ZH = :partChineseName) C, SJZX.EPC_PICTURE D\n" +
                " WHERE C.PARTNUMBER = D.PARTNUMBER(+)";

        return toJSONArray(find(sSQL, param));
    }

    /***
     * 通过版本 NAME_ZH PARTNUMBER_ZH EPC_BBMC 查询所有关联的partNumber并去重
     */
    public JSONArray searchPartNumberForParseSign(JSONObject param) {
        String sSQL = "SELECT DISTINCT A.PARTNUMBER\n" +
                "  FROM SJZX.EPC_"+ param.getString("epcVersion") +" A, SJZX.EPC_STD_CATA B\n" +
                " WHERE A.CURRENT_ID = B.CURRENT_ID\n" +
                "   AND A.MY_MODEL_STD_ID = B.MY_MODEL_STD_ID\n" +
                "   AND B.MJBJ = '1'\n" +
                "   AND B.NAME_ZH = :menuName\n" +
                "   AND B.EPC_BBMC = :epcVersion\n" +
                "   AND A.PARTNAME_ZH = :partChineseName AND A.PARTNUMBER IS NOT NULL";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 批量更新相关的配件编译标记为1
     */
    public int updateParseSignToTrue(JSONObject param) {
        String sSQL ="UPDATE EPC_OE_PJMC C\n" +
                "   SET C.BYBJ = 1, C.TS01 = :TS01\n" +
                " WHERE C.PARTNUMBER IN (SELECT DISTINCT A.PARTNUMBER\n" +
                "                          FROM SJZX.EPC_"+ param.getString("epcVersion") +" A, SJZX.EPC_STD_CATA B\n" +
                "                         WHERE A.CURRENT_ID = B.CURRENT_ID\n" +
                "                           AND A.MY_MODEL_STD_ID = B.MY_MODEL_STD_ID\n" +
                "                           AND B.MJBJ = '1'\n" +
                "                           AND B.NAME_ZH = :menuName\n" +
                "                           AND B.EPC_BBMC = :epcVersion\n" +
                "                           AND A.PARTNAME_ZH = :partChineseName\n" +
                "                           AND A.PARTNUMBER IS NOT NULL)\n";
        return update(sSQL, param);
    }

    /***
     * 查询系统中存在的配件数据
     */
    public JSONObject queryPJMC(JSONObject param) {
        String sSQL = "SELECT T.PJMCID, T.PJMC\n" +
                "  FROM SJZX.MAS_PJMC T WHERE T.PJMCID = :pjmcid";

        JSONArray tempArray =  toJSONArray(find(sSQL, param));
        if( tempArray == null || tempArray.size() == 0 ) throw new RuntimeException(param.getString("pjmcid") + "在系统中未找到对应的名称");
        return tempArray.getJSONObject(0);
    }


    /***
     *  删除原始解析数据
     */
    public int deleteParseData(JSONObject param) {
        String sSQL = "DELETE FROM EPC_OE_PJMC\n" +  //SJZX.
                " WHERE PARTNUMBER = :partNumber\n" +
                "   AND EPC_BBMC = :epcVersion";
        return delete(sSQL, param);
    }

    /***
     * 写入原始解析数据
     */
    public int insertParseData(JSONObject param) {
        String sSQL = "INSERT INTO EPC_OE_PJMC\n" +
                "  (EPC_BBMC, PARTNUMBER, PJMCID, PJMC, LRR, TS01, BYBJ)\n" +
                " VALUES " +
                "  (:EPC_BBMC, :PARTNUMBER, :PJMCID, :PJMC, :LRR,\n" +
                "   :TS01, :BYBJ)";
        return update(sSQL, param);
    }


    /***
     * 查询配置车辆信息
     */
    public JSONArray searchVehicleConfig(JSONObject param) {
        String sSQL = "SELECT T.PZCXID, T.PZCXMC, T.FDJXH, T.NK,\n" +
                "       DECODE(T.QDFS, 0, '前轮驱动', 1, '后轮驱动', 2, '四轮驱动', 3, '适时四驱', 4, '全时四驱',\n" +
                "               5, '分时四驱', '') QDFS, T.RLLX, T.QCCPMC, T.CHXMC\n" +
                "  FROM SJZX.MAS_QCCXPZ T\n" +
                " WHERE PZCXID = :vehicleConfig";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 查询配件名称
     */
    public JSONArray searchPartDescription(JSONObject param) {
        String sSQL = "SELECT T.PJMCID id, T.PJMC name, T.PPSPBJ matchProductSign\n" +
                "  FROM SJZX.MAS_PJMC T\n" +
                " WHERE T.PJMCID = :key\n" +
                "    OR T.PJMC LIKE '%"+ param.get("key") +"%'";
        return toJSONArray(find(sSQL, param));
    }


    /***
     * 查询该配件对应的精友配件名称和外部渠道名称
     */
    public JSONObject searchThirdPartyPartDescription(JSONObject param) {
        String sSQL = "SELECT *\n" +
                "  FROM (SELECT DBMS_LOB.SUBSTR(WM_CONCAT(T.PJMC), 4000) PARTDESCRIPTIONJINGYOU\n" +
                "           FROM SJZX.EPC_OE_PJMC_JINGYOU T\n" +
                "          WHERE T.PARTNUMBER = :partNumber),\n" +
                "       \n" +
                "       (SELECT DBMS_LOB.SUBSTR(WM_CONCAT(T.PJMC), 4000) PARTDESCRIPTIONEXT\n" +
                "           FROM SJZX.EPC_OE_PJMC_EXT T\n" +
                "          WHERE T.PARTNUMBER = :partNumber)\n" +
                " WHERE 1 = 1";
        JSONArray tempArray = toJSONArray(find(sSQL, param));
        if(tempArray.isEmpty()) return new JSONObject();
        return tempArray.getJSONObject(0);
    }

}
